package DBLayer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import ModelLayer.Invoice;

public class DBInvoice implements IFDBInvoice{
	private Connection con;
	public DBInvoice(){
		con = DBConnection.getInstance().getDBcon();
	}
	public void createInvoice(Invoice invoice) throws SQLException {
		String query = "INSERT into Invoice values(" +
				invoice.getinvoiceNo()+",'" +
				invoice.getpaymentDate()+"'," +
				invoice.getamount()+"," +
				invoice.getdiscount()+"," +
				invoice.getdelivery()+")";
		      System.out.println("insert : " + query);		  					
		      try{ // insert new product
		            Statement stmt = con.createStatement();
			 	  	stmt.setQueryTimeout(5);
			 	  	stmt.executeUpdate(query);
			 	  	stmt.close();
				}//end try	
			 	catch(SQLException ex){
			 	  	System.out.println("Insert exception in invoice db: "+ex);
			   }
	}
	public void updateInvoice(Invoice invoice) throws SQLException {
		String query = "UPDATE Invoice set" +
				"paymentDate = '"+invoice.getpaymentDate()+"'" +
				"amount = "+invoice.getamount()+
				"discount = "+invoice.getdiscount() +
				"delivery = "+invoice.getdelivery()+
				"WHERE invoiceNo = "+invoice.getinvoiceNo();
		      System.out.println("Update : " + query);		  					
		      try{ 
		            Statement stmt = con.createStatement();
			 	  	stmt.setQueryTimeout(5);
			 	  	stmt.executeUpdate(query);
			 	  	stmt.close();
				}
			 	catch(SQLException ex){
			 	  	System.out.println("Update exception in invoice db: "+ex);
			   }
	}
	public void deleteInvoice(int invoiceNo) throws SQLException {
		String query = "DELETE from Invoice where invoiceNo = "+invoiceNo;
		System.out.println("Delete : " + query);		  					
	      try{ 
	            Statement stmt = con.createStatement();
		 	  	stmt.setQueryTimeout(5);
		 	  	stmt.executeUpdate(query);
		 	  	stmt.close();
			}
		 	catch(SQLException ex){
		 	  	System.out.println("Delete exception in invoice db: "+ex);
		   }
	}
	public Invoice findInvoice(int invoiceID) throws SQLException {
		String wClause = "invoiceID = "+invoiceID;
		return singleWhere(wClause);
	}
	private Invoice singleWhere(String wClause)
	{
		ResultSet results;
		Invoice invoiceObj = new Invoice();
	    String query =  buildQuery(wClause);
        System.out.println(query);
		try{ // read the product from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
	 			invoiceObj = buildInvoice(results);
                            stmt.close();
			}
		}	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return invoiceObj;
	}
	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM Invoice ";
		
		if (wClause.length()>0)
			query= query+"WHERE "+ wClause;
			
		return query;
	}
	private Invoice buildInvoice(ResultSet results)
    {   
		Invoice invoiceObj = new Invoice();
        try{
        	invoiceObj.setinvoiceNo(results.getInt(1));
        	invoiceObj.setpaymentDate(results.getString(2));
        	invoiceObj.setamount(results.getDouble(3));
        	invoiceObj.setdiscount(results.getInt(4));
        	invoiceObj.setdelivery(results.getDouble(5));
        }
       catch(Exception e)
       {
           System.out.println("Error in building the invoice object");
       }
       return invoiceObj;
    }
}